package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IUserDao;
import com.weixin.entity.User;
import com.weixin.util.C3p0Util;

public class UserDaoImpl implements IUserDao {
	C3p0Util c3p0=new C3p0Util();
	
	@Override
	public List<User> selectByRolePage(String role, int pageSize, int pageNum) throws SQLException {
		String sql="SELECT * FROM USER u, role r, user_role ur WHERE u.u_id = ur.u_id AND r.r_id = ur.r_id AND r.title =? LIMIT ?, ?";
		return c3p0.getqr().query(sql, new BeanListHandler<User>(User.class),role,(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int selectCount(String role) throws SQLException {
		String sql="SELECT COUNT(*) FROM USER u, role r, user_role ur WHERE u.u_id = ur.u_id AND r.r_id = ur.r_id AND r.title =?";
		return (int)(long)c3p0.getqr().query(sql, new ScalarHandler(),role);
	}

	@Override
	public List<User> selectAllByRole(String role) throws SQLException {
		String sql="SELECT * FROM USER u, role r, user_role ur WHERE u.u_id = ur.u_id AND r.r_id = ur.r_id AND r.title =?";
		return c3p0.getqr().query(sql, new BeanListHandler<User>(User.class),role);
	}
	
}
